from sqlalchemy import create_engine, Column, Numeric, String, Table, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from settings import *

mysql_url = MYSQL_URI or f'mysql+pymysql://{MYSQL_USERNAME}:{MYSQL_PASSWORD}@{MYSQL_URI}:{MYSQL_PORT}/{MYSQL_DATABASE}'
engine = create_engine(mysql_url)
Base = declarative_base()
metadata = Base.metadata
metadata.bind = engine
session = sessionmaker(bind=engine)()

table_between = Table("between_table", metadata,
                      Column("student_id", Integer, ForeignKey('info_students.id'), primary_key=True),
                      Column("teacher_id", Integer, ForeignKey('info_teachers.id'), primary_key=True))


class Student(Base):
    __tablename__ = 'info_students'
    id = Column(Integer, unique=True, primary_key=True)
    name = Column(String(length=20), nullable=False)
    number = Column(Integer)
    chinese = Column(Numeric(10, 1))
    math = Column(Numeric(10, 1))
    english = Column(Numeric(10, scale=1))

    def __repr__(self) -> str:
        return f'<Student id={self.id} name={self.name} number={self.number} chinese={self.chinese} math={self.math} english={self.english}>'



class Teacher(Base):
    __tablename__ = 'info_teachers'
    id = Column(Integer, unique=True, primary_key=True)
    name = Column(String(length=20), nullable=False)
    password = Column(String(length=16))
    students = relationship("Student", backref='teachers', secondary=table_between)
    def __repr__(self) -> str:
        return f'<Teacher id={self.id} name={self.name}>'


if __name__ == '__main__':
    metadata.drop_all(engine)
    metadata.create_all(engine)